SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[TMCNTA_COBR] AS

/*************************** SELECT PARA TMDOCU_CLIE ********************************/
Select t1.CO_EMPR, t1.CO_CLIE, t2.NO_CLIE NO_AUXI, t1.CO_TIPO_DOCU, t1.NU_DOCU_CLIE, t1.FE_EMIS, t1.FE_VENC, 
	t1.CO_MONE, t1.FA_TIPO_CAMB, t2.NU_RUCS, 
	    (PATINDEX(t8.ST_SIGN, 'S') - PATINDEX(t8.ST_SIGN, 'N')) * t1.IM_TOTA 'IM_TOTA', 
	    ((PATINDEX(t8.ST_SIGN, 'S') - PATINDEX(t8.ST_SIGN, 'N')) * t1.IM_TOTA ) -
     	    (PATINDEX(t8.ST_SIGN, 'S') - PATINDEX(t8.ST_SIGN, 'N')) *
	    ((Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU,0) +
		  	    PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0)),0)
		From TCMOVI_BANC t11, TDMOVI_BANC t12, TMCNTA_BANC t13
		Where t1.CO_EMPR = t12.CO_EMPR
		And t1.CO_TIPO_DOCU = t12.TI_DOCU_ENTI
		And t1.NU_DOCU_CLIE = t12.NU_DOCU_ENTI
		And t11.TI_SITU != 'ANU'
		And t11.CO_EMPR = t12.CO_EMPR
		And t11.CO_BANC = t12.CO_BANC
		And t11.NU_CNTA_BANC  = t12.NU_CNTA_BANC
		And t11.AA_BNCO = t12.AA_BNCO
		And t11.MM_BNCO = t12.MM_BNCO
		And t11.TI_MOVI_BANC  = t12.TI_MOVI_BANC
		And t11.NU_COMP_BANC = t12.NU_COMP_BANC
		And t11.CO_EMPR = t13.CO_EMPR
		And t11.CO_BANC = t13.CO_BANC
		And t11.NU_CNTA_BANC  = t13.NU_CNTA_BANC) +

	(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU,0) +
		  	PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0)),0)
	From TCMOVI_CAJA t11, TDMOVI_CAJA t12, TMCAJA t13
	Where t1.CO_EMPR = t12.CO_EMPR
	And t1.CO_TIPO_DOCU = t12.TI_DOCU_ENTI
	And t1.NU_DOCU_CLIE = t12.NU_DOCU_ENTI
	And t11.TI_SITU != 'ANU'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_CAJA = t12.CO_CAJA
	And t11.AA_CAJA = t12.AA_CAJA
	And t11.MM_CAJA = t12.MM_CAJA
	And t11.TI_MOVI  = t12.TI_MOVI
	And t11.NU_COMP_CAJA = t12.NU_COMP_CAJA
	And t11.CO_EMPR = t13.CO_EMPR
	And t11.CO_CAJA = t13.CO_CAJA) +
	(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_COBR_CDES,0) +
		  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_CDES,0)),0)
	From TCAMAR_NCCL t11, TDAMAR_NCCL t12
	Where t1.CO_EMPR = t12.CO_EMPR
	And t1.CO_TIPO_DOCU = t12.CO_TIDO_DEST
	And t1.NU_DOCU_CLIE = t12.NU_DOCU_DEST
	And t11.TI_SITU != 'ANU'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_UNID_CONC = t12.CO_UNID_CONC
	And t11.NU_AMAR = t12.NU_AMAR
	And t11.FE_AMAR = t12.FE_AMAR) +

	(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_COBR_CORI,0) +
		  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_CORI,0)),0)
	From TCAMAR_NCCL t11, TDAMAR_NCCL t12
	Where t1.CO_EMPR = t12.CO_EMPR
	And t1.CO_TIPO_DOCU = t12.CO_TIDO_ORIG
	And t1.NU_DOCU_CLIE = t12.NU_DOCU_ORIG
	And t11.TI_SITU != 'ANU'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_UNID_CONC = t12.CO_UNID_CONC
	And t11.NU_AMAR = t12.NU_AMAR
	And t11.FE_AMAR = t12.FE_AMAR) +

	(Select ISNULL(SUM( PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CANJ_MDOC,0) +
		  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)
	From TCCANJ_CLIE t11, TDCANJ_CLIE t12
	Where t1.CO_EMPR = t12.CO_EMPR
	And t1.CO_TIPO_DOCU = t12.CO_TIPO_DOCU
	And t1.NU_DOCU_CLIE = t12.NU_DOCU_CLIE
	And t11.TI_SITU != 'ANU'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_UNID_CONC = t12.CO_UNID_CONC
	And t11.CO_CLIE = t12.CO_CLIE
	And t11.NU_CANJ = t12.NU_CANJ
	And t11.FE_CANJ = t12.FE_CANJ) +

	(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_RENO_MDOC,0) +
		  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)
	From TCRENO_CLIE t11, TDRENO_CLIE t12
	Where t1.CO_EMPR = t12.CO_EMPR
	And t1.CO_TIPO_DOCU = t12.CO_TIPO_DOCU
	And t1.NU_DOCU_CLIE = t12.NU_DOCU_CLIE
	And t11.TI_SITU != 'ANU'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_UNID_CONC = t12.CO_UNID_CONC
	And t11.CO_CLIE = t12.CO_CLIE
	And t11.NU_RENO = t12.NU_RENO
	And t11.FE_RENO = t12.FE_RENO) +

	(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU, 0) +
		  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)
	From TCAMAR_ANTC t11, TDAMAR_ANTC t12
	Where t1.CO_EMPR = t12.CO_EMPR
	And t1.CO_TIPO_DOCU = t12.TI_DOCU_ENTI
	And t1.NU_DOCU_CLIE = t12.NU_DOCU_ENTI
	And t11.TI_SITU != 'ANU'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_UNID_CONC = t12.CO_UNID_CONC
	And t11.NU_AMAR = t12.NU_AMAR
	And t11.FE_AMAR = t12.FE_AMAR)) IM_SALD, t1.DE_OBSE, T1.CO_ESTA_DOCU
From TMDOCU_CLIE t1, TMCLIE t2, TTDOCU_CNTB t8 
Where t1.CO_ESTA_DOCU != 'ANU'
And t1.CO_EMPR = t2.CO_EMPR
And t1.CO_CLIE = t2.CO_CLIE
And t1.CO_TIPO_DOCU = t8.TI_DOCU
And t8.ST_DOCU_0017 = 'N'
And t8.ST_DOCU_0015 = 'N'

/************************ SELECT PARA LETRAS ************************************/
Union All
Select t1.CO_EMPR, t1.CO_CLIE, t2.NO_CLIE, 'LET', t1.NU_LETR_CLIE NU_DOCU_CLIE,
	t1.FE_EMIS, t1.FE_VENC, t1.CO_MONE,  t1.FA_TIPO_CAMB, t2.NU_RUCS, 
	t1.IM_TOTA, t1.IM_TOTA -
	((Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU,0) +
		  	    PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0)),0)
	From TCMOVI_BANC t11, TDMOVI_BANC t12, TMCNTA_BANC t13
	Where t1.CO_EMPR = t12.CO_EMPR
	And t1.NU_LETR_CLIE = t12.NU_DOCU_ENTI
	And t11.TI_SITU != 'ANU'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_BANC = t12.CO_BANC
	And t11.NU_CNTA_BANC  = t12.NU_CNTA_BANC
	And t11.AA_BNCO = t12.AA_BNCO
	And t11.MM_BNCO = t12.MM_BNCO
	And t11.TI_MOVI_BANC  = t12.TI_MOVI_BANC
	And t11.NU_COMP_BANC = t12.NU_COMP_BANC
	And t11.CO_EMPR = t13.CO_EMPR
	And t11.CO_BANC = t13.CO_BANC
	And t11.NU_CNTA_BANC  = t13.NU_CNTA_BANC) +

	(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU,0) +
		  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0)),0)
	From TCMOVI_CAJA t11, TDMOVI_CAJA t12, TMCAJA t13
	Where t1.CO_EMPR = t12.CO_EMPR
	And t1.NU_LETR_CLIE = t12.NU_DOCU_ENTI
	And t11.TI_SITU != 'ANU'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_CAJA = t12.CO_CAJA
	And t11.AA_CAJA = t12.AA_CAJA
	And t11.MM_CAJA = t12.MM_CAJA
	And t11.TI_MOVI  = t12.TI_MOVI
	And t11.NU_COMP_CAJA = t12.NU_COMP_CAJA
	And t11.CO_EMPR = t13.CO_EMPR
	And t11.CO_CAJA = t13.CO_CAJA) +

	(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_COBR_CDES,0) +
		  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_CDES,0)),0)
	From TCAMAR_NCCL t11, TDAMAR_NCCL t12
	Where t1.CO_EMPR = t12.CO_EMPR
	And t1.NU_LETR_CLIE = t12.NU_DOCU_DEST
	And t11.TI_SITU != 'ANU'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_UNID_CONC = t12.CO_UNID_CONC
	And t11.NU_AMAR = t12.NU_AMAR
	And t11.FE_AMAR = t12.FE_AMAR) +

	(Select ISNULL(SUM( PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CANJ_MDOC,0) +
		  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)
	From TCCANJ_CLIE t11, TDCANJ_CLIE t12
	Where t1.CO_EMPR = t12.CO_EMPR
	And t1.NU_LETR_CLIE = t12.NU_DOCU_CLIE
	And t11.TI_SITU != 'ANU'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_UNID_CONC = t12.CO_UNID_CONC
	And t11.CO_CLIE = t12.CO_CLIE
	And t11.NU_CANJ = t12.NU_CANJ
	And t11.FE_CANJ = t12.FE_CANJ) +
		   
	(Select ISNULL(SUM( PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_RENO_MDOC,0) +
		  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)
	From TCRENO_CLIE t11, TDRENO_CLIE t12
	Where t1.CO_EMPR = t12.CO_EMPR
	And t1.NU_LETR_CLIE = t12.NU_DOCU_CLIE
	And t11.TI_SITU != 'ANU'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_UNID_CONC = t12.CO_UNID_CONC
	And t11.CO_CLIE = t12.CO_CLIE
	And t11.NU_RENO = t12.NU_RENO
	And t11.FE_RENO = t12.FE_RENO) +

	(Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU, 0) +
		  	   PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)
	From TCAMAR_ANTC t11, TDAMAR_ANTC t12
	Where t1.CO_EMPR = t12.CO_EMPR
	And t1.NU_LETR_CLIE = t12.NU_DOCU_ENTI
	And t11.TI_SITU != 'ANU'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_UNID_CONC = t12.CO_UNID_CONC
	And t11.NU_AMAR = t12.NU_AMAR
	And t11.FE_AMAR = t12.FE_AMAR)) IM_SALD, '', T1.CO_ESTA_DOCU
From TMLETR_CLIE t1, TMCLIE t2
Where t1.CO_ESTA_DOCU != 'ANU'
And t1.CO_EMPR = t2.CO_EMPR
And t1.CO_CLIE = t2.CO_CLIE

/**************************** SUBSELECT PARA ANTICIPOS BANCOS *************************/
Union All
Select t1.CO_EMPR, t1.CO_ENTI, t2.NO_CLIE, t1.CO_TIPO_DOCU, t1.NU_DOCU_BANC, 
	t1.FE_OPER, t1.FE_OPER, t3.CO_MONE, t1.FA_TIPO_CAMB, t2.NU_RUCS, 
	t1.IM_MOVI * -1, (t1.IM_MOVI -
	(Select ISNULL(SUM(ISNULL(t12.IM_DETA_NEGA,0)),0)
	From TCAMAR_ANTC t11, TDAMAR_ANTC t12
	Where T1.CO_EMPR = T11.CO_EMPR
	And T1.CO_BANC = T11.CO_BANC
	And T1.NU_CNTA_BANC = T11.NU_CNTA_BANC
	And T1.AA_BNCO = T11.AA_BNCO
	And T1.MM_BNCO = T11.MM_BNCO
	And T1.NU_COMP_BANC = T11.NU_COMP_BANC
	And t11.TI_SITU = 'ACT'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_UNID_CONC = t12.CO_UNID_CONC
	And t11.NU_AMAR = t12.NU_AMAR
	And t11.FE_AMAR = t12.FE_AMAR)) * -1,
	t1.DE_OBSE, ''
From TCMOVI_BANC t1,  TTTIPO_OPER t4 , TMCNTA_BANC t3, TMCLIE t2
Where t1.TI_MOVI_BANC = 'I'
And t1.TI_SITU = 'ACT'
And t1.CO_EMPR = t4.CO_EMPR
And t1.CO_TIPO_OPER = t4.CO_TIPO_OPER
And t4.ST_AUXI_0002 = 'S' 
And t1.CO_EMPR = t3.CO_EMPR
And t1.CO_BANC = t3.CO_BANC
And t1.NU_CNTA_BANC  = t3.NU_CNTA_BANC
And t3.TI_SITU = 'ACT'
And t1.CO_EMPR = t2.CO_EMPR
And t1.CO_ENTI = t2.CO_CLIE

/**************************** SUBSELECT PARA ANTICIPOS CAJA**************************/

Union All
Select t1.CO_EMPR, t1.CO_ENTI, t2.NO_CLIE, t5.TI_DOCU, t1.CO_CAJA + ' - ' +  t1.NU_COMP_CAJA,
	t1.FE_OPER, t1.FE_OPER, t3.CO_MONE, t1.FA_TIPO_CAMB, t2.NU_RUCS, 
	t1.IM_MOVI * -1,  ( t1.IM_MOVI -
	(Select ISNULL(SUM(ISNULL(t12.IM_DETA_NEGA,0)),0)
	From TCAMAR_ANTC t11, TDAMAR_ANTC t12
	Where T1.CO_EMPR = T11.CO_EMPR
	And T1.CO_CAJA = T11.CO_CAJA
	And T1.AA_CAJA = T11.AA_CAJA
	And T1.MM_CAJA = T11.MM_CAJA
	And T1.NU_COMP_CAJA = T11.NU_COMP_CAJA
	And t11.TI_SITU = 'ACT'
	And t11.CO_EMPR = t12.CO_EMPR
	And t11.CO_UNID_CONC = t12.CO_UNID_CONC
	And t11.NU_AMAR = t12.NU_AMAR
	And t11.FE_AMAR = t12.FE_AMAR)) * -1,
	t1.DE_OBSE, ''
From TCMOVI_CAJA t1, TTTIPO_OPER t4, TMCAJA t3, TMCLIE t2, TTDOCU_CNTB t5
Where t1.TI_MOVI = 'I'
And t1.TI_SITU = 'ACT'
And t1.CO_EMPR = t4.CO_EMPR
And t1.CO_TIPO_OPER = t4.CO_TIPO_OPER
And t4.ST_AUXI_0002 = 'S' 
And t1.CO_EMPR = t3.CO_EMPR
And t1.CO_CAJA = t3.CO_CAJA
And t3.TI_SITU = 'ACT'
And t1.CO_EMPR = t2.CO_EMPR
And t1.CO_ENTI = t2.CO_CLIE
AnD t5.ST_DOCU_0017 = 'S'

GO
